package com.link.base.base.consumer.util.poi;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;

import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 对excel数据操作
 * @author yipeng.zhu@hand-china.com
 * @date: 2018/11/5 16:33
 * @version 1.0
 */

public class PoiUtil {

    private static final Pattern PATTERNINNER = Pattern.compile("(\\d{4})-(\\d+)-(\\d+).*");

    /**
     * 读取Excel数据
     * @param clazz
     * @param sheet
     * @param titleMap
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     */

    public static <T> List<T> getBeanFromExcelRow(Class<T> clazz, Sheet sheet, Map<String, Object> titleMap) throws InstantiationException,
            IllegalAccessException, InvocationTargetException, NoSuchMethodException {
        List<T> result = new ArrayList<T>();
        //获取标题行
        Row rowZero = sheet.getRow(0);
        int collumnCount = rowZero.getPhysicalNumberOfCells();
        //从第二行开始读取
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            T t = clazz.newInstance();
            Row row = sheet.getRow(rowNum);
            if (null != row) {
                for (int num = 0; num < collumnCount; num++) {
                    for (String header : titleMap.keySet()) {
                        if (getStringValueFromCell(rowZero.getCell(num)).equals(header)) {
                            PropertyDescriptor propertyDescriptor = BeanUtilsBean.getInstance()
                                    .getPropertyUtils().getPropertyDescriptor(t, titleMap.get(header).toString());
                            if (propertyDescriptor != null) {
                                BeanUtils.setProperty(t, titleMap.get(header).toString(), getStringValueFromCell(row.getCell(num)));
                                break;
                            }
                        }
                    }
                }
                result.add(t);
            }
        }
        return result;
    }

    /**
     * Excel单元数据转换成字符串
     * @author yipeng.zhu@hand-china.com
     * @date 2019-12-29
     * @param cell
     * @return 数据字符串
     */

    public static String getStringValueFromCell(Cell cell) {
        SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd");
        DecimalFormat decimalFormat = new DecimalFormat("#.#");
        String cellValue = "";
        if (null != cell) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case XSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        double d = cell.getNumericCellValue();
                        Date date = HSSFDateUtil.getJavaDate(d);
                        cellValue = sFormat.format(date);
                    } else {
                        cellValue = decimalFormat.format((cell.getNumericCellValue()));
                    }
                    break;
                case Cell.CELL_TYPE_BLANK:
                    cellValue = "";
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_ERROR:
                    cellValue = "";
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    cellValue = cell.getCellFormula().toString();
                    break;
                default:
                    break;
            }
            return cellValue.trim();
        } else {
            return cellValue.trim();
        }
    }

    /**
     * 校验字符串是否符合日期时间格式 yyyy-mm-dd hh:mm:ss
     *
     * @param timeStr
     * @return
     */

    public static boolean valiDateTimeWithLongFormat(String timeStr) {
        String format = "((19|20)[0-9]{2})-(0?[1-9]|1[012])-(0?[1-9]|[12][0-9]|3[01]) "
                + "([01]?[0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]";
        Pattern pattern = Pattern.compile(format);
        Matcher matcher = pattern.matcher(timeStr);
        if (matcher.matches()) {
            pattern = PATTERNINNER;
            matcher = pattern.matcher(timeStr);
            if (matcher.matches()) {
                int y = Integer.valueOf(matcher.group(1));
                int m = Integer.valueOf(matcher.group(2));
                int d = Integer.valueOf(matcher.group(3));
                int len = 28;
                if (d > len) {
                    Calendar c = Calendar.getInstance();
                    c.set(y, m - 1, 1);
                    int lastDay = c.getActualMaximum(Calendar.DAY_OF_MONTH);
                    return (lastDay >= d);
                }
            }
            return true;
        }
        return false;
    }

    /**
     * 校验字符串是否符合日期格式 yyyy-mm-dd
     *
     * @param timeStr
     * @return
     */
    public static boolean valiDateWithLongFormat(String timeStr) {
        String format = "((19|20)[0-9]{2})-(0?[1-9]|1[012])-(0?[1-9]|[12][0-9]|3[01])";
        Pattern pattern = Pattern.compile(format);
        Matcher matcher = pattern.matcher(timeStr);
        if (matcher.matches()) {
            pattern = PATTERNINNER;
            matcher = pattern.matcher(timeStr);
            if (matcher.matches()) {
                int y = Integer.valueOf(matcher.group(1));
                int m = Integer.valueOf(matcher.group(2));
                int d = Integer.valueOf(matcher.group(3));
                int len = 28;
                if (d > len) {
                    Calendar c = Calendar.getInstance();
                    c.set(y, m - 1, 1);
                    int lastDay = c.getActualMaximum(Calendar.DAY_OF_MONTH);
                    return (lastDay >= d);
                }
            }
            return true;
        }
        return false;
    }

    /**
     * 验证手机号
     * @author yipeng.zhu@hand-china.com
     * @date 2019-12-29
     * @param phoneNum
     * @return boolean
     */

    public static boolean valiPhoneNumFormat(String phoneNum) {
        String format = "^((17[0-9])|(14[0-9])|(13[0-9])|(15[^4,\\D])|(18[0,5-9]))\\d{8}$";
        Pattern pattern = Pattern.compile(format);
        Matcher matcher = pattern.matcher(phoneNum);
        if (matcher.matches()) {
            return true;
        }
        return false;
    }
}
